//
//  SQLiteDataBaseManager.m
//  18-SQLiteHomeWork
//
//  Created by 曹曹 on 16/8/8.
//  Copyright © 2016年 曹曹. All rights reserved.
//

#import "SQLiteDataBaseManager.h"
#import "Contacts.h"

@implementation SQLiteDataBaseManager
+ (SQLiteDataBaseManager *)sharedManager {
    static SQLiteDataBaseManager *manager = nil;
    static dispatch_once_t onceToken;
    dispatch_once(&onceToken, ^{
        manager = [[SQLiteDataBaseManager alloc] init];
    });
    return manager;
}
#pragma mark - open
- (BOOL)openSQLite {
    NSArray *pathArray = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
    NSString *documentPath = [pathArray firstObject];
    NSString *dbPath = [documentPath stringByAppendingPathComponent:@"student.db"];
    NSLog(@"dbPath:%@", dbPath);
    int result = sqlite3_open(dbPath.UTF8String, &dbPointer);
    return [self isOperationResult:result alertText:@"打开"];
}
- (BOOL)closeSQLite {
    int result = sqlite3_close(dbPointer);
    return [self isOperationResult:result alertText:@"关闭"];
}
#pragma mark - create table
- (BOOL)createTable {
    NSString *createSQL = @"create table stu(stu_name text primary key, stu_phone text, stu_address text, stu_avatar text)";
    int result = sqlite3_exec(dbPointer, createSQL.UTF8String, NULL, NULL, NULL);
    return [self isOperationResult:result alertText:@"创建表"];
}
#pragma mark - insert
- (BOOL)insertCon:(Contacts *)con {
    NSString *insertSQL = [NSString stringWithFormat:@"insert into stu(stu_name, stu_phone, stu_address, stu_avatar) values ('%@', '%@', '%@', '%@')",con.nameString, con.phoneNumber, con.addressString, con.headImageString];
    int result = sqlite3_exec(dbPointer, insertSQL.UTF8String, NULL, NULL, NULL);
    return [self isOperationResult:result alertText:@"插入数据"];;
}
#pragma mark - delete
- (BOOL)deleteCon:(Contacts *)con {
    NSString *deleteSQL = [NSString stringWithFormat:@"delete from stu where stu_name = '%@'", con.nameString];
    int result = sqlite3_exec(dbPointer, deleteSQL.UTF8String, NULL, NULL, NULL);
    NSString *alertText = @"删除数据";
    return [self isOperationResult:result alertText:alertText];;
}
#pragma mark - select
- (NSArray *)selectAll {
    NSString *selectAll = @"select * from stu";
    sqlite3_stmt *stmt = NULL;
    int result = sqlite3_prepare(dbPointer, selectAll.UTF8String, -1, &stmt, NULL);
    NSMutableArray *stuArray = [NSMutableArray array];
    if (result == SQLITE_OK) {
        while (sqlite3_step(stmt) == SQLITE_ROW) {
            const unsigned char *stu_name = sqlite3_column_text(stmt, 0);
            const unsigned char *stu_phone = sqlite3_column_text(stmt, 1);
            const unsigned char *stu_address = sqlite3_column_text(stmt, 2);
            const unsigned char *stu_avatar = sqlite3_column_text(stmt, 3);
            Contacts *con = [[Contacts alloc] init];
            con.nameString = [NSString stringWithUTF8String:(const char *)stu_name];
            con.phoneNumber = [NSString stringWithUTF8String:(const char *)stu_phone];
            con.addressString = [NSString stringWithUTF8String:(const char *)stu_address];
            NSString *avatar = [NSString stringWithUTF8String:(const char *)stu_avatar];
            con.headImageString = avatar;
            [stuArray addObject:con];
        }
    }
    // 循环结束处理完数据后, 销毁替身
    sqlite3_finalize(stmt);
    return stuArray;
}
- (BOOL)isOperationResult:(int)result alertText:(NSString *)text {
    BOOL isOperation = YES;
    if (result == SQLITE_OK) {
        NSLog(@"数据库%@成功", text);
        isOperation = YES;
    } else {
        NSLog(@"数据库%@失败----错误码:%d", text, result);
        isOperation = NO;
    }
    return isOperation;
}


@end
